﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

using Husb.Data;
using Husb.DataUtil;

using GroundWater.Data;
using MonitoringTable = GroundWater.Data.MonitoringDataSet.MonitoringDataTable;
using MonitoringRow = GroundWater.Data.MonitoringDataSet.MonitoringRow;

namespace GroundWater.DataAccess
{
    public class MonitoringDataAdapter : DataAccessAdapter<MonitoringDataSet, MonitoringTable, MonitoringRow>
    {
        public MonitoringDataAdapter()
        {
            this.TableName = "Monitoring";

            GetAllStoredProc = SqlStatements.Monitoring_FetchAll;
            InsertStoredProc = SqlStatements.Monitoring_Insert;
            UpdateStoredProc = SqlStatements.Monitoring_Update;
            DeleteStoredProc = SqlStatements.Monitoring_Delete;

            GetByIdStoredProc = SqlStatements.Monitoring_GetById;

            GetRowCountStoredProc = SqlStatements.Monitoring_FetchRowCount;

            //FetchByDatePaged = ;
        }

        protected override void PopulateInsertParameters(Database db, DbCommand cmd)
        {
            db.AddInParameter(cmd, "WellCode", DbType.String, "WellCode", DataRowVersion.Current);
            db.AddInParameter(cmd, "MonitorTime", DbType.DateTime, "MonitorTime", DataRowVersion.Current);
            db.AddInParameter(cmd, "Waterlevel", DbType.Decimal, "Waterlevel", DataRowVersion.Current);
            db.AddInParameter(cmd, "FlowRate", DbType.Decimal, "FlowRate", DataRowVersion.Current);
            db.AddInParameter(cmd, "HydraulicPressure", DbType.Decimal, "HydraulicPressure", DataRowVersion.Current);

            db.AddInParameter(cmd, "WaterQuality", DbType.String, "WaterQuality", DataRowVersion.Current);
            db.AddInParameter(cmd, "SectionPlane", DbType.String, "SectionPlane", DataRowVersion.Current);
            db.AddInParameter(cmd, "SIMCard", DbType.String, "SIMCard", DataRowVersion.Current);
            db.AddInParameter(cmd, "BTUAddress", DbType.String, "BTUAddress", DataRowVersion.Current);
            db.AddInParameter(cmd, "WellName", DbType.String, "WellName", DataRowVersion.Current);

            db.AddInParameter(cmd, "CellVoltage", DbType.String, "CellVoltage", DataRowVersion.Current);
            db.AddInParameter(cmd, "OriginalWaterLevel", DbType.String, "OriginalWaterLevel", DataRowVersion.Current);
        }


        //protected override void PopulateStringIdParameters(Database db, DbCommand cmd, string id)
        //{
        //    db.AddInParameter(cmd, "Code", DbType.String, id);// DbType.AnsiString
        //}

        //protected override void PopulateDeleteParameters(Database db, DbCommand cmd)
        //{
        //    db.AddInParameter(cmd, "Code", DbType.String, "Code", DataRowVersion.Original);
        //}



        public MonitoringTable FetchPaged(string wellCode, string startDate, string endDate, string sortExpression, int maximumRows, int startRowIndex)
        {
            if (startRowIndex == 0) startRowIndex++;
            int end = startRowIndex + maximumRows - 1;
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchPaged, wellCode, startDate, endDate, sortExpression, startRowIndex.ToString(), end.ToString());
            MonitoringTable table = this.GetTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        public int FetchRowCount(string wellCode, string startDate, string endDate)//, string sortExpression, int maximumRows, int startRowIndex
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchRowCount, wellCode, startDate, endDate);
            object o = this.GetValue(sql, new List<DatabaseParameter>(), false, false, null);
            if (o == null) return 0;

            return Convert.ToInt32( o);
        }

        public MonitoringTable FetchByDatePaged(string startDate, string endDate, string sortExpression, int maximumRows, int startRowIndex)
        {
            if (startRowIndex == 0) startRowIndex++;
            int end = startRowIndex + maximumRows - 1;
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchByDatePaged, startDate, endDate, sortExpression, startRowIndex.ToString(), end.ToString());
            MonitoringTable table = this.GetTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        public int FetchByDateRowCount(string startDate, string endDate)//, string sortExpression, int maximumRows, int startRowIndex
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchByDateRowCount, startDate, endDate);
            object o = this.GetValue(sql, new List<DatabaseParameter>(), false, false, null);
            if (o == null) return 0;

            return Convert.ToInt32(o);
        }

        public DataTable FetchSectionPlane()
        {
            DataTable table = DataAccessUtil.GetDataTable(SqlStatements.Monitoring_FetchSectionPlane, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        public DataTable FetchWaterLeveGroupByWellCode(string sectionPlane, string startDate, string endDate)
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchWaterLeveGroupByWellCode, sectionPlane, startDate, endDate);
            DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        public DataTable FetchWaterLeveGroupByDate(string wellCode, string startDate, string endDate)
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchWaterLeveGroupByDate, wellCode, startDate, endDate);
            DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        public DataTable FetchWaterLeveByWellCode(string wellCode, string startDate, string endDate)
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchWaterLeveByWellCode, wellCode, startDate, endDate);
            DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        public DataTable FetchWaterLeveAllByDate(string startDate, string endDate)
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchWaterLeveAllByDate, startDate, endDate);
            DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }


        public DataTable FetchWaterLeveBySectionPlane(string sectionPlane, string startDate, string endDate)
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchWaterLeveBySectionPlane, sectionPlane, startDate, endDate);
            DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        public DataTable FetchWaterLeveAllBySectionPlane(string startDate, string endDate)
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchWaterLeveAllBySectionPlane, startDate, endDate);
            DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        /// <summary>
        /// 根据起止日期获取 按行政区域分组的水位数据
        /// </summary>
        /// <param name="startDate">起始日期</param>
        /// <param name="endDate">截至日期</param>
        /// <returns></returns>
        public DataTable FetchWaterLeveByDivisionsCode(string startDate, string endDate)
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchWaterLeveByDivisionsCode, startDate, endDate);
            DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        /// <summary>
        /// 根据起止日期和行政区域 获取水位数据
        /// </summary>
        /// <param name="divisionsCode"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataTable FetchWaterLeveAvgByDivisionsCode(string divisionsCode, string startDate, string endDate)
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchWaterLeveAvgByDivisionsCode, startDate, endDate);
            DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        public MonitoringRow FetchById(string wellCode, DateTime monitoringTime)
        {
            List<DatabaseParameter> ps = new List<DatabaseParameter>();
            ps.Add(new DatabaseParameter("WellCode", DbType.String, wellCode));
            ps.Add(new DatabaseParameter("MonitorTime", DbType.DateTime, monitoringTime));

            DataTable table = GetTable(SqlStatements.Monitoring_FetchById, ps, false, false, null);

            return table.Rows.Count > 0 ? (MonitoringRow)table.Rows[0] : null;

        }



        public int Delete(string wellCode, DateTime monitoringTime)
        {
            List<DatabaseParameter> ps = new List<DatabaseParameter>();
            ps.Add(new DatabaseParameter("Code", DbType.String, wellCode));
            ps.Add(new DatabaseParameter("MonitorTime", DbType.DateTime, monitoringTime));

            int cnt = Husb.DataUtil.DataAccessUtil.ExecuteNonQuery(DeleteStoredProc, ps, false, false);

            return cnt;
        }


        public MonitoringTable FetchWaterLeveToday(string date)
        {
            if (date == "Now")
            {
                date = DateTime.Now.ToString("yyyy-MM-dd");
            }
            string endDate = DateTime.Parse(date).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchToday, date, endDate);
            MonitoringTable table = this.GetTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }


        public DataTable FetchWaterLeveByDate(string startDate, string endDate)
        {
            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");
            string sql = String.Format(SqlStatements.Monitoring_FetchByDate, startDate, endDate);
            DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        public DataTable FetchWaterLeveDataByDate(string startDate, string endDate)
        {
            string sql = String.Format(SqlStatements.Monitoring_FetchDataByDate, startDate, endDate);
            DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);
            return table;
        }

        public int UpdateConcurrencyByDate(string startDate, string endDate)
        {
            string sql = String.Format(SqlStatements.Monitoring_UpdateConcurrencyByDate, startDate, endDate);
            int  cnt = DataAccessUtil.ExecuteSqlString(sql);
            return cnt;
        }

        public MonitoringTable FetchCriteria(string startDate, string endDate, decimal? minWaterLevel, decimal? maxWaterLevel, decimal? voltage)
        {

            endDate = DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd");

            string criteria = "";
            if (minWaterLevel != null)
            {
                criteria += " AND m.Z <= " + minWaterLevel.ToString();
            }

            if (maxWaterLevel != null)
            {
                criteria += " AND m.Z >= " + maxWaterLevel.ToString();
            }

            if (voltage != null)
            {
                criteria += " AND m.CELL <= " + voltage.ToString();
            }

            string sql = String.Format(SqlStatements.Monitoring_FetchCriteria, startDate, endDate, criteria);

            MonitoringTable table = this.GetTable(sql, new List<DatabaseParameter>(), false, false);
            //DataTable table = DataAccessUtil.GetDataTable(sql, new List<DatabaseParameter>(), false, false, null);

            if (table == null || table.Rows.Count == 0)
                return table;

            MonitoringTable tmp = new MonitoringDataSet.MonitoringDataTable();
            tmp.ImportRow(table[0]);

            string wellCode = table[0].WellCode;
            string date = table[0].MonitorTime.ToString("yyyy-MM-dd");
            int p = 1;
            while (p < table.Count)
            {
                if (wellCode == table[p].WellCode && date == table[p].MonitorTime.ToString("yyyy-MM-dd"))
                {
                    p++;
                    continue;
                }
                else
                {
                    tmp.ImportRow(table[p]);
                    wellCode = table[p].WellCode;
                    date = table[p].MonitorTime.ToString("yyyy-MM-dd");
                }
                p++;
            }


            return tmp;
        }

        /// <summary>
        /// 获取所有行政区域代码
        /// </summary>
        /// <returns></returns>
        public DataTable FetchAllDivisionsCode()
        {
            DataTable table = DataAccessUtil.GetDataTable(SqlStatements.Well_FetchAllDivisionsCode, new List<DatabaseParameter>(), false, false, null);
            return table;
        }
    }
}
